﻿using System;
using Fly.Data;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace FlyWebFrame.Model
{
    public class BaseModel: SqlServer
    {
        
        public Result SelectJoin(string table2, string fk, string where, string choice, string order, string group)
        {
            try
            {
                if (where != "" && where != null)
                {
                    where = "WHERE " + where + " AND ";
                }
                if (choice == "" || choice == null)
                {
                    choice = "*";
                }
                if (order != "" && order != null)
                {
                    order = "ORDER BY " + order;
                }
                if (group != "" && group != null)
                {
                    group = "GROUP BY " + group;
                }
                where += " " + table2 + "." + fk + " = " + this.table + ".Id";
                SqlConnection connection = getSqlConnection();
                SqlDataAdapter adapter = new SqlDataAdapter("SELECT " + choice + " FROM [" + this.table + "], " + table2 + " " + where + " " + group + " " + order, connection);
                
                connection.Open();
                DataSet dataset = new DataSet();
                adapter.Fill(dataset, this.table);
                connection.Close();
                if (dataset.Tables[0].Rows.Count > 0)
                {
                    dynamic[] dyn = new dynamic[dataset.Tables[0].Rows.Count];
                    for (int i = 0; i < dyn.Length; i++)
                    {
                        Type mytype = this.GetType();
                        dyn[i] = Activator.CreateInstance(mytype);

                        for (int j = 0; j < dataset.Tables[0].Columns.Count; j++)
                        {
                            if (dataset.Tables[0].Rows[i][dataset.Tables[0].Columns[j].ColumnName] == DBNull.Value)
                            {
                                dyn[i].GetType().GetField(dataset.Tables[0].Columns[j].ColumnName).SetValue(dyn[i], null);
                            }
                            else
                            {
                                dyn[i].GetType().GetField(dataset.Tables[0].Columns[j].ColumnName).SetValue(dyn[i], dataset.Tables[0].Rows[i][dataset.Tables[0].Columns[j].ColumnName]);
                            }
                        }
                    }
                    return new Result(true, null, dyn);
                }
                else
                {
                    return new Result(false, "Not found", null);
                }
            }
            catch (Exception ex)
            {
                return new Result(false, "Database error", ex);
            }
        }

    }
}